﻿Imports System.Data.OleDb
Imports System.Data.SqlClient
Namespace QuanLyKhachHang
    Public Class frmThongKeXuatKho
        Private ReportType As Integer = 0
        Private IndexCurrentMonth As Integer = 0
        Private bEdit As Boolean
        Private Sub frmThongKeXuatKho_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Try
                Dim i As Integer
                For i = 0 To dgvThongKeNhap.ColumnCount - 1
                    dgvThongKeNhap.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
                    dgvThongKeNhap.ColumnHeadersHeight = 21
                    dgvThongKeNhap.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter
                Next
                KMergeHeaderDGV1.Grid = dgvThongKeNhap
                DisableControl()
                dtpTheoNgay.Enabled = True
                Nam_Load()
                Thang_Load()
                Report_TheoNgay()
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
        Sub DisableControl()
            dtpTheoNgay.Enabled = False
            cbxTheoThang.Enabled = False
            cbxNamTheoThang.Enabled = False
            cbxTheoNam.Enabled = False
            dtpTuNgay.Enabled = False
            dtpDenNgay.Enabled = False
            txtSoPhieu.Enabled = False
            cbxNamHoaDon.Enabled = False
        End Sub

        Private Sub rbtTheoNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNgay.CheckedChanged
            DisableControl()
            dtpTheoNgay.Enabled = True
            ReportType = 0
        End Sub
        Private IsPostBack As Boolean = False
        Private Sub rbtTheoThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoThang.CheckedChanged
            DisableControl()
            cbxTheoThang.Enabled = True
            cbxNamTheoThang.Enabled = True
            If IsPostBack = False Then
                cbxTheoThang.SelectedIndex = IndexCurrentMonth
            End If
            cbxNamTheoThang.Enabled = True
            If IsPostBack = False Then
                cbxNamTheoThang.SelectedIndex = 0
            End If
            ReportType = 1
            IsPostBack = True
        End Sub
        Private IsPostBack2 As Boolean = False
        Private Sub rbtTheoNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNam.CheckedChanged
            DisableControl()
            cbxTheoNam.Enabled = True
            If IsPostBack2 = False Then
                cbxTheoNam.SelectedIndex = 0
            End If
            ReportType = 2
            IsPostBack2 = True
        End Sub

        Private Sub rbtTuNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTuNgay.CheckedChanged
            DisableControl()
            dtpTuNgay.Enabled = True
            dtpDenNgay.Enabled = True
            ReportType = 3
        End Sub
        Private Sub rbtTheoPhieu_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoPhieu.CheckedChanged
            Try
                DisableControl()
                txtSoPhieu.Enabled = True
                cbxNamHoaDon.Enabled = True
                ReportType = 4
            Catch ex As Exception

            End Try
        End Sub
        Private Sub dgvThongKeNhap_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles dgvThongKeNhap.Paint
            KMergeHeaderDGV1.HeaderText = "Chứng từ xuất"
            KMergeHeaderDGV1.Merge(e, 0, 1)
            KMergeHeaderDGV1.HeaderText = "Chủng loại vật tư"
            KMergeHeaderDGV1.Merge(e, 2, 4)
            KMergeHeaderDGV1.HeaderText = FormatNumber(TongXuat, True)
            KMergeHeaderDGV1.Merge(e, 5, 5)
            KMergeHeaderDGV1.HeaderText = FormatNumber(TongXuatDVT, True)
            KMergeHeaderDGV1.Merge(e, 6, 6)
            KMergeHeaderDGV1.HeaderText = "Phần xuất vật tư"
            KMergeHeaderDGV1.Merge(e, 7, 7)
        End Sub
#Region "Thông kê xuất kho"
        Private TongXuat As Single = 0
        Private TongXuatDVT As Single = 0
        Public Sub Report_TheoNgay()
            Try
                Dim listXuatKho As List(Of ThongKeXuatKhoInfo)
                Dim listTongXuatKho As List(Of TongXuatKhoInfo)
                Dim controller As New ThongKeXuatKhoBLL
                Dim TuNgay As Integer = ConvertDateToInt(dtpTheoNgay.Value.ToString.Split(" ")(0))
                Dim ToiNgay As Integer = ConvertDateToInt(dtpTheoNgay.Value.ToString.Split(" ")(0))
                Dim MaSo As String = txtTenSanPham.Text
                listXuatKho = controller.ThongKeXuatKho(TuNgay, ToiNgay, MaSo)
                'tính TongXuat | TongXuatDVT
                listTongXuatKho = controller.ThongKeXuatKho_TongXuat(TuNgay, ToiNgay, MaSo)
                For Each item As TongXuatKhoInfo In listTongXuatKho
                    TongXuat = item.TongXuat
                    TongXuatDVT = item.TongXuatDVT
                Next
                'format
                For Each item As ThongKeXuatKhoInfo In listXuatKho
                    item.NgayNhapKho = Format(DateTime.Parse(item.NgayNhapKho), "dd/MM/yyyy")
                    item.SoLuong = Format(Single.Parse(item.SoLuong), "N2")
                    item.SoLuongTheoDVT = Format(Single.Parse(item.SoLuongTheoDVT), "N2")
                Next
                dgvThongKeNhap.AutoGenerateColumns = False
                dgvThongKeNhap.DataSource = listXuatKho
            Catch ex As Exception
                ShowError(ex)
            End Try
        End Sub
        Public Sub Report_TheoThang()
            Try
                Dim Thang As MonthInfo
                Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                If Thang Is Nothing Then
                    MessageBox.Show("Bạn chưa chọn tháng.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub
                End If
                Dim Nam As YearInfo
                Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
                If Nam Is Nothing Then
                    MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub
                End If
                Dim sNgayCuoiThang As String
                Dim sThang As String
                Dim DaysInMonth As Integer = DateTime.DaysInMonth(Nam.TenNam, Thang.TenThang)
                If DaysInMonth <= 9 Then
                    sNgayCuoiThang = "0" & DaysInMonth.ToString()
                Else
                    sNgayCuoiThang = DaysInMonth.ToString()
                End If
                If Integer.Parse(Thang.TenThang) <= 9 Then
                    sThang = "0" & Thang.TenThang
                Else
                    sThang = Thang.TenThang
                End If
                Dim TuNgay As Integer
                Dim ToiNgay As Integer
                Dim listXuatKho As List(Of ThongKeXuatKhoInfo)
                Dim listTongXuatKho As List(Of TongXuatKhoInfo)
                Dim controller As New ThongKeXuatKhoBLL
                Dim MaSo As String = txtTenSanPham.Text
                TuNgay = Integer.Parse(Nam.TenNam & sThang & "01")
                ToiNgay = Integer.Parse(Nam.TenNam & sThang & sNgayCuoiThang)
                listXuatKho = controller.ThongKeXuatKho(TuNgay, ToiNgay, MaSo)
                'tính TongXuat | TongXuatDVT
                listTongXuatKho = controller.ThongKeXuatKho_TongXuat(TuNgay, ToiNgay, MaSo)
                For Each item As TongXuatKhoInfo In listTongXuatKho
                    TongXuat = item.TongXuat
                    TongXuatDVT = item.TongXuatDVT
                Next
                'format
                For Each item As ThongKeXuatKhoInfo In listXuatKho
                    item.NgayNhapKho = Format(DateTime.Parse(item.NgayNhapKho), "dd/MM/yyyy")
                    item.SoLuong = Format(Single.Parse(item.SoLuong), "N2")
                    item.SoLuongTheoDVT = Format(Single.Parse(item.SoLuongTheoDVT), "N2")
                Next
                dgvThongKeNhap.AutoGenerateColumns = False
                dgvThongKeNhap.DataSource = listXuatKho
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
        Public Sub Report_TheoNam()
            Try
                Dim Nam As YearInfo
                Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                If Nam Is Nothing Then
                    MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub
                End If
                Dim TuNgay As Integer = Integer.Parse(Nam.TenNam & "01" & "01")
                Dim ToiNgay As Integer = Integer.Parse(Nam.TenNam & "12" & "30")
                Dim listXuatKho As List(Of ThongKeXuatKhoInfo)
                Dim listTongXuatKho As List(Of TongXuatKhoInfo)
                Dim controller As New ThongKeXuatKhoBLL
                Dim MaSo As String = txtTenSanPham.Text
                listXuatKho = controller.ThongKeXuatKho(TuNgay, ToiNgay, MaSo)
                'tính TongXuat | TongXuatDVT
                listTongXuatKho = controller.ThongKeXuatKho_TongXuat(TuNgay, ToiNgay, MaSo)
                For Each item As TongXuatKhoInfo In listTongXuatKho
                    TongXuat = item.TongXuat
                    TongXuatDVT = item.TongXuatDVT
                Next
                'format
                For Each item As ThongKeXuatKhoInfo In listXuatKho
                    item.NgayNhapKho = Format(DateTime.Parse(item.NgayNhapKho), "dd/MM/yyyy")
                    item.SoLuong = Format(Single.Parse(item.SoLuong), "N2")
                    item.SoLuongTheoDVT = Format(Single.Parse(item.SoLuongTheoDVT), "N2")
                Next
                dgvThongKeNhap.AutoGenerateColumns = False
                dgvThongKeNhap.DataSource = listXuatKho
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
        Public Sub Report_TuNgayDenNgay()
            Try
                Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
                Dim ToiNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
                Dim listXuatKho As List(Of ThongKeXuatKhoInfo)
                Dim listTongXuatKho As List(Of TongXuatKhoInfo)
                Dim controller As New ThongKeXuatKhoBLL
                Dim MaSo As String = txtTenSanPham.Text
                listXuatKho = controller.ThongKeXuatKho(TuNgay, ToiNgay, MaSo)
                'tính TongXuat | TongXuatDVT
                listTongXuatKho = controller.ThongKeXuatKho_TongXuat(TuNgay, ToiNgay, MaSo)
                For Each item As TongXuatKhoInfo In listTongXuatKho
                    TongXuat = item.TongXuat
                    TongXuatDVT = item.TongXuatDVT
                Next
                'format
                For Each item As ThongKeXuatKhoInfo In listXuatKho
                    item.NgayNhapKho = Format(DateTime.Parse(item.NgayNhapKho), "dd/MM/yyyy")
                    item.SoLuong = Format(Single.Parse(item.SoLuong), "N2")
                    item.SoLuongTheoDVT = Format(Single.Parse(item.SoLuongTheoDVT), "N2")
                Next
                dgvThongKeNhap.AutoGenerateColumns = False
                dgvThongKeNhap.DataSource = listXuatKho
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
        Public Sub Report_SoHoaDon()
            Try
                Dim NamHoaDon As YearInfo
                NamHoaDon = CType(cbxNamHoaDon.SelectedItem, YearInfo)
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dtThongKeNhapKho As New DataTable
                Dim query As String
                query = "Select DH.So,DH.NgayBan As NgayNhapKho,SP.MaSanPham As MaSo,SP.TenSanPham As TenGoi,CT.SoLuong AS SoLuong,DH.TenKhachHang As DienGiai" & _
                        " From DonHangBan as DH" & _
                        " Left Outer Join ChiTietDonHangBan As CT On DH.IdDonHangBan = CT.IDDonHangBan" & _
                        " Left Outer Join SanPham As SP On Sp.IDSanPham = CT.IDSanPham" & _
                        " Where CT.Xoa=0 And DH.Xoa=0 And CT.LoaiPhi = N'HangHoa' And CT.TrangThai=N'Đã duyệt' And DH.So=N'" & txtSoPhieu.Text & "' And DH.NamHoaDon=" & NamHoaDon.Nam & "  Order by Cast(DH.So As Int) DESC"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKeNhapKho)
                dbConn.Close()
                'filter by code
                Dim dtFilterByProductCode As DataTable = dtThongKeNhapKho.Clone()
                If txtTenSanPham.Text <> "" Then
                    For Each dr As DataRow In dtThongKeNhapKho.Rows
                        'check 
                        If dr.Item("MaSo") = txtTenSanPham.Text Then
                            dtFilterByProductCode.ImportRow(dr)
                        End If
                    Next
                Else
                    dtFilterByProductCode = dtThongKeNhapKho
                End If
                TongXuat = 0
                For i As Integer = 0 To dtFilterByProductCode.Rows.Count - 1
                    dtFilterByProductCode.Rows(i)("NgayNhapKho") = Format(DateTime.Parse(dtFilterByProductCode.Rows(i)("NgayNhapKho")), "dd/MM/yyyy")
                    TongXuat = TongXuat + Single.Parse(dtFilterByProductCode.Rows(i)("SoLuong").ToString)
                Next
                dgvThongKeNhap.AutoGenerateColumns = False
                dgvThongKeNhap.DataSource = dtFilterByProductCode
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
#End Region
#Region "Thống kê"
        Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
            Try
                Select Case ReportType
                    Case 0
                        Report_TheoNgay()
                    Case 1
                        Report_TheoThang()
                    Case 2
                        Report_TheoNam()
                    Case 3
                        Report_TuNgayDenNgay()
                    Case 4
                        Report_SoHoaDon()
                End Select
            Catch ex As Exception

            End Try
        End Sub
#End Region
#Region "Load combo tháng và combo năm"
#Region "Sub Class"
        Private Class YearInfo
            Dim _Nam As Integer
            Dim _TenNam As String
            Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
                _Nam = Nam
                _TenNam = TenNam
            End Sub
            Public Function Nam() As Integer
                Return _Nam
            End Function
            Public Function TenNam() As String
                Return _TenNam
            End Function
            Public Overrides Function ToString() As String
                Return _TenNam
            End Function
        End Class
        Private Class MonthInfo
            Dim _Thang As Integer
            Dim _TenThang As String
            Public Sub New(ByVal Thang As Integer, ByVal TenThang As String)
                _Thang = Thang
                _TenThang = TenThang
            End Sub
            Public Function Thang() As Integer
                Return _Thang
            End Function
            Public Function TenThang() As String
                Return _TenThang
            End Function
            Public Overrides Function ToString() As String
                Return _TenThang
            End Function
        End Class
#End Region
#Region "Load data"
        Private Sub Nam_Load()
            Dim currentYear As Integer = Now.Year
            cbxTheoNam.Items.Clear()
            cbxNamHoaDon.Items.Clear()
            Try
                While (currentYear >= 2011)
                    cbxTheoNam.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                    cbxNamTheoThang.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                    cbxNamHoaDon.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                    currentYear = currentYear - 1
                End While
                cbxNamHoaDon.SelectedIndex = 0
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Sub Thang_Load()
            Dim currentYear As Integer = 1
            cbxTheoThang.Items.Clear()
            Try
                Dim i As Integer = 0
                While (currentYear <= 12)
                    cbxTheoThang.Items.Add(New MonthInfo(currentYear, currentYear.ToString))
                    If currentYear = Now.Month Then
                        IndexCurrentMonth = i
                    End If
                    currentYear = currentYear + 1
                    i = i + 1
                End While
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
#End Region
#End Region


        Private Sub cmdInThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdInThongKe.Click
            'Try
            Try
                frmSaveFile.FileType = ".xls"
                ShowModalForm(frmSaveFile, False, False)
                Dim result As Boolean
                result = frmSaveFile.Result
                If result Then
                    Dim Path As String = frmSaveFile.Path
                    Dim FileName As String = frmSaveFile.FileName
                    ExportExcel(Path, FileName & ".xls")
                End If
                frmSaveFile.txtPath.Text = ""
                frmSaveFile.txtFile.Text = ""
            Catch ex As Exception

            End Try
        End Sub
#Region "Export Excel"
        Private Sub ExportExcel(ByVal Path As String, ByVal FileName As String)
            Try
                Dim SubTitle As String
                If rbtTheoNgay.Checked Then
                    SubTitle = "Ngày " & formatDateVN2String(dtpTheoNgay.Value)
                ElseIf rbtTheoThang.Checked Then
                    Dim Thang As MonthInfo
                    Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                    Dim Nam As YearInfo
                    Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
                    SubTitle = " Tháng " & Thang.TenThang & " Năm " & Nam.TenNam
                ElseIf rbtTheoNam.Checked Then
                    Dim Nam As YearInfo
                    Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                    SubTitle = " Năm " & Nam.TenNam
                ElseIf rbtTuNgay.Checked Then
                    SubTitle = " Từ ngày " & formatDateVN2String(dtpTuNgay.Value.ToString.Split(" ")(0)) & " Đến ngày " & formatDateVN2String(dtpDenNgay.Value.ToString.Split(" ")(0))
                End If
                'write
                'read from template
                Dim CurrentDir As String = System.IO.Path.GetDirectoryName(Application.ExecutablePath)
                Dim PathTemPlate As String
                PathTemPlate = CurrentDir + "\TemplateExcel\ThongKeXuatKho_Header.txt"
                Dim Header As String = String.Format(System.IO.File.ReadAllText(PathTemPlate), SubTitle, Format(TongXuat, "N02"), Format(TongXuatDVT, "N02"))
                ExportToFile(Path, FileName, Header)
                'content
                Dim contentExcelXML As String = ""
                'first(row)
                If dgvThongKeNhap.Rows.Count > 0 Then
                    contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                            "<Cell ss:Index=""" & "3""" & " ss:StyleID=""" & "s50""" & "><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(0, 0).Value & "</Data></Cell>" & vbCrLf & _
                            "<Cell ss:StyleID=""" & "s50""" & "><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(1, 0).Value & "</Data></Cell>" & vbCrLf & _
                            "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(2, 0).Value & "</Data></Cell>" & vbCrLf & _
                            "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(3, 0).Value & "</Data></Cell>" & vbCrLf & _
                            "<Cell ss:StyleID=""" & "s50""" & "><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(4, 0).Value & "</Data></Cell>" & vbCrLf & _
                            "<Cell ss:StyleID=""" & "s51""" & "><Data ss:Type=""" & "Number" & """>" & Double.Parse(dgvThongKeNhap(5, 0).Value) & "</Data></Cell>" & vbCrLf & _
                            "<Cell ss:StyleID=""" & "s51""" & "><Data ss:Type=""" & "Number" & """>" & Double.Parse(dgvThongKeNhap(6, 0).Value) & "</Data></Cell>" & vbCrLf & _
                            "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(7, 0).Value & "</Data></Cell>" & vbCrLf & _
                        "</Row>"
                    ExportToFile(Path, FileName, contentExcelXML)
                End If
                'contentExcelXML = DevideRowGrid()
                Dim EndRow As Integer = dgvThongKeNhap.Rows.Count - 1
                Dim StartRow As Integer = 0
                Dim K As Integer = EndRow \ 100
                Dim SoDu As Integer = EndRow Mod 100
                Dim aK(100) As Integer
                For i As Integer = 1 To 100
                    If K * i <= EndRow Then
                        aK(i) = K * i
                    End If
                Next
                aK(100) = 100 * K + SoDu
                Dim sRet As String = ""
                frmBusyLoading.Show()
                frmBusyLoading.progress.Minimum = 0
                frmBusyLoading.progress.Maximum = 100
                For i As Integer = 0 To 99
                    sRet = ConvertExcel(aK(i) + 1, aK(i + 1))
                    If sRet <> "" Then
                        ExportToFile(Path, FileName, sRet)
                    End If
                    frmBusyLoading.progress.Value = i
                Next
                frmBusyLoading.Close()
                'end content
                PathTemPlate = CurrentDir + "\TemplateExcel\ThongKeXuatKho_End.txt"
                Dim Footer As String = System.IO.File.ReadAllText(PathTemPlate)
                ExportToFile(Path, FileName, Footer)
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub
        Private Function ConvertExcel(ByVal iBegin As Integer, ByVal iEnd As Integer) As String
            Try
                Dim contentExcelXML As String = ""
                For i As Integer = iBegin To iEnd
                    contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                        "<Cell ss:Index=""" & "3""" & " ss:StyleID=""" & "s50""" & "><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(0, i).Value & "</Data></Cell>" & vbCrLf & _
                        "<Cell ss:StyleID=""" & "s50""" & "><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(1, i).Value & "</Data></Cell>" & vbCrLf & _
                        "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(2, i).Value & "</Data></Cell>" & vbCrLf & _
                        "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(3, i).Value & "</Data></Cell>" & vbCrLf & _
                        "<Cell ss:StyleID=""" & "s50""" & "><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(4, i).Value & "</Data></Cell>" & vbCrLf & _
                        "<Cell ss:StyleID=""" & "s51""" & "><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(5, i).Value & "</Data></Cell>" & vbCrLf & _
                        "<Cell ss:StyleID=""" & "s51""" & "><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(6, i).Value & "</Data></Cell>" & vbCrLf & _
                        "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(7, i).Value & "</Data></Cell>" & vbCrLf & _
                    "</Row>"
                Next
                Return contentExcelXML
            Catch ex As Exception

            End Try
        End Function
#End Region
#Region "Tìm kiếm sản phẩm"
        Private Sub SanPham_Search(ByVal KeyWord As String)
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsSanPham As New DataSet
                Dim dtSanPham As New DataTable
                Dim query As String = "SELECT * FROM (Select SanPham.DVTSanPhamId,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa From SanPham " & _
                                        " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                        " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                        " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                        " Where SanPham.Xoa=0 And SanPham.MaSanPham Like N'" & KeyWord & "%') AS SP WHERE SP.DVTSANPHAMID IS NOT NULL Order By TenSanPham"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsSanPham)
                dbConn.Close()
                dgvSanPham_Search.AutoGenerateColumns = False
                dtSanPham = dsSanPham.Tables(0)
                dtSanPham.Columns.Add("STT")
                For i As Integer = 0 To dtSanPham.Rows.Count - 1
                    dtSanPham.Rows(i)("STT") = i + 1
                    'chú giải
                Next

                dgvSanPham_Search.DataSource = dtSanPham
                If dgvSanPham_Search.Rows.Count - 1 > 0 Then
                    dgvSanPham_Search.Rows(0).Selected = True
                End If
            Catch ex As Exception
                ShowError()
            End Try
        End Sub

        Private Sub txtTenSanPham_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyDown
            Try
                If e.KeyCode = Keys.Enter Then
                    Dim TenSanPham As String
                    Dim DVT As String
                    TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                    DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                    txtTenSanPham.Text = TenSanPham
                    'hide search panel
                    PanelSanPham_Search.Visible = False
                    cmdThongKe.Focus()
                End If
            Catch ex As Exception

            End Try
        End Sub



        Private Sub txtTenSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyUp
            Try
                If txtTenSanPham.Text <> "" Then
                    If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                        dgvSanPham_Search.Focus()
                    End If
                End If
                txtTenSanPham.BackColor = Color.White
            Catch ex As Exception

            End Try
        End Sub
        Private Sub dgvSanPham_Search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvSanPham_Search.KeyDown
            Try
                Select Case e.KeyCode
                    Case Keys.Down
                        PanelSanPham_Search.Visible = True
                    Case Keys.Up
                        PanelSanPham_Search.Visible = True
                    Case Keys.Enter
                        Dim TenSanPham As String
                        Dim DVT As String
                        TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                        DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                        txtTenSanPham.Text = TenSanPham
                        'hide search panel
                        PanelSanPham_Search.Visible = False
                        cmdThongKe.Focus()
                    Case Else
                        txtTenSanPham.Focus()
                End Select
            Catch ex As Exception

            End Try
        End Sub

        Private Sub txtTenSanPham_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenSanPham.TextChanged
            Try
                If txtTenSanPham.Text <> "" Then
                    'show search panel
                    If Not bEdit Then
                        PanelSanPham_Search.Visible = True
                        SanPham_Search(txtTenSanPham.Text)
                    Else
                        PanelSanPham_Search.Visible = False
                    End If
                Else
                    'hide search panel
                    PanelSanPham_Search.Visible = False
                End If
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
        Private Sub dgvSanPham_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham_Search.CellDoubleClick
            Try
                'Gan Du Lieu
                Dim TenSanPham As String
                Dim DVT As String
                TenSanPham = dgvSanPham_Search.Rows(e.RowIndex).Cells("MaSanPham").Value.ToString
                DVT = dgvSanPham_Search.Rows(e.RowIndex).Cells("TenDonViTinh").Value.ToString
                txtTenSanPham.Text = TenSanPham
                'hide search panel
                PanelSanPham_Search.Visible = False
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
#End Region
    End Class
End Namespace